package com.rbn.stockmobiledevices.estadisticas.internal;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.transaction.annotation.Transactional;

import com.rbn.stockmobiledevices.entradas.Entrada;
import com.rbn.stockmobiledevices.estadisticas.EstadisticaManager;
import com.rbn.stockmobiledevices.estadisticas.Ganancia;
import com.rbn.stockmobiledevices.estadisticas.Stock;
import com.rbn.stockmobiledevices.salidas.Venta;
import com.rbn.stockmobiledevices.utils.AutoCompleteElement;

public class HibernateEstadisticaManager implements EstadisticaManager {
	
	private SessionFactory sessionFactory;
	private static final Logger logger = Logger.getLogger(HibernateEstadisticaManager.class);
	
	public HibernateEstadisticaManager(SessionFactory sessionFactory) {
		this.sessionFactory = sessionFactory;
	}
	
	@Transactional(readOnly = true)
	@SuppressWarnings("unchecked")
	public List<Stock> getAllStock() {
		logger.info("IN > getAllStock");
		try{
			Session session = getCurrentSession();
			String hlqQuery = "from Stock s";
			hlqQuery += " order by s.fechaEntrada";
			Query q = session.createQuery(hlqQuery);
			return q.list();
		}finally{
			logger.info("OUT < getAllStock");
		}
	}
	
	@SuppressWarnings("unchecked")
	@Transactional(readOnly = true)
	public List<Entrada> getEntradasImpagas(Integer startPosition, Integer pageSize) {
		Session session = getCurrentSession();
		String hlqQuery = "from Entrada e";
		hlqQuery += " where e.pagado = 0 ";
		hlqQuery += " order by e.fechaEntrada desc, e.imei";

		Query q = session.createQuery(hlqQuery);
		if(startPosition!=null && pageSize!=null){
			q.setFirstResult(startPosition);
			q.setMaxResults(pageSize);
		}
		
		return q.list();
	}
	
	@SuppressWarnings("unchecked")
	@Transactional(readOnly = true)
	public List<Venta> getVentasImpagas(Integer startPosition, Integer pageSize) {
		Session session = getCurrentSession();
		String hlqQuery = "from Venta v";
		hlqQuery += " where v.pagado = 0 ";
		hlqQuery += " order by v.fechaVenta desc, v.imei";

		Query q = session.createQuery(hlqQuery);
		if(startPosition!=null && pageSize!=null){
			q.setFirstResult(startPosition);
			q.setMaxResults(pageSize);
		}
		return q.list();
	}
	
	@SuppressWarnings("unchecked")
	@Transactional(readOnly = true)
	public List<Stock> getPaginadoStock(Integer startPosition, Integer pageSize,
			String imei, String dispositivo, Long proveedor, Date fecha) {
		Session session = getCurrentSession();
		String hlqQuery = "from Stock s";

		String whereClause = "";
		if((imei!=null && imei.length()>0) || (dispositivo !=null && dispositivo.length()>0) || proveedor!=null || fecha!=null){
			hlqQuery += " where ";
		
			if (imei != null && imei.length() > 0) {
				whereClause += "and  s.imei like :imei ";
			}
			if(dispositivo !=null && dispositivo.length()>0){
				whereClause += "and  s.dispositivo.modelo like :dispositivo ";
			}
			if(proveedor != null){
				whereClause += "and  s.proveedor.id = :proveedor ";
			}
			if(fecha != null){
				whereClause += "and  s.fechaEntrada between :fecha_desde and :fecha_hasta ";
			}
			
			whereClause = whereClause.substring(whereClause.indexOf("and ")+4 ,whereClause.length());
			hlqQuery += whereClause;
		}
		
		hlqQuery += " order by s.fechaEntrada desc, s.imei";
		Query q = session.createQuery(hlqQuery);
		
		if (imei != null && imei.length() > 0) {
			q.setString("imei", imei);
		}
		if(dispositivo !=null && dispositivo.length()>0){
			q.setString("dispositivo", "%"+dispositivo+"%");
		}
		if(proveedor != null){
			q.setLong("proveedor", proveedor);
		}
		if(fecha != null){
			Date fecha_desde = null;
			Date fecha_hasta = null;
			GregorianCalendar cal = new GregorianCalendar();
			cal.setTime(fecha);
			cal.set(Calendar.HOUR, 0);
			cal.set(Calendar.MINUTE, 0);
			cal.set(Calendar.SECOND, 0);
			cal.set(Calendar.MILLISECOND, 0);
			
			fecha_desde = cal.getTime();
			cal.add(Calendar.HOUR, 23);
			cal.add(Calendar.MINUTE, 59);
			cal.add(Calendar.SECOND, 59);
			
			fecha_hasta = cal.getTime(); 
			q.setDate("fecha_desde", fecha_desde);
			q.setDate("fecha_hasta", fecha_hasta);
		}
		
		if(startPosition!=null && pageSize!=null){
			q.setFirstResult(startPosition);
			q.setMaxResults(pageSize);
		}
		
		return q.list();
	}
	
	@Transactional(readOnly = true)
	@SuppressWarnings("unchecked")
	public List<Ganancia> getGanancia(int agno){
		logger.info("IN > getGanancia");
		try{
			Session session = getCurrentSession();
			String hlqQuery = "from Ganancia g";
			hlqQuery += " where g.agno = :agno";
			hlqQuery += " order by g.agno, g.mes";
			Query q = session.createQuery(hlqQuery);
			q.setInteger("agno", agno);
			return q.list();
		}finally{
			logger.info("OUT < getGanancia");
		}
	}
	
	@Transactional(readOnly = true)
	public Double getTotalCapital() {
		logger.info("IN > getTotalCapital");
		Double result = null;
		try{
			Session session = getCurrentSession();
			String hlqQuery = "SELECT sum(s.precioCompra) from Stock s ";
			
			Query q = session.createQuery(hlqQuery);
			result = (Double) q.uniqueResult();
			
			return result;
		}finally{
			logger.info("OUT < getTotalCapital");
		}
	}
	
	@Transactional(readOnly = true)
	@SuppressWarnings("unchecked")
	public List<Object[]> getStockMinimoList(Integer startPosition, Integer pageSize){
		logger.info("IN > getStockMinimoList");
		try{
			Session session = getCurrentSession();
			
			String hlqQuery = "SELECT count(s.imei), d.modelo, d.stockMinimo " +
					"from Stock s right outer join s.dispositivo d " +
					"where d.stockMinimo != 0 " +
					"group by d.modelo, d.stockMinimo having d.stockMinimo>=count(s.imei)";
			
			Query q = session.createQuery(hlqQuery);
			
			if(startPosition!=null && pageSize!=null){
				q.setFirstResult(startPosition);
				q.setMaxResults(pageSize);
			}
			return q.list();
		}finally{
			logger.info("OUT < getStockMinimoList");
		}
	}
	
	// get the last 4 years
	public List<AutoCompleteElement> getAgnos() {
		logger.info("IN > getAgnos");
		try{
			List<AutoCompleteElement> list = new ArrayList<AutoCompleteElement>();
			Calendar cal = new GregorianCalendar();
			cal.setTime(new Date());
			
			String year = "";
			for(int i=0;i<4;i++){
				year = String.valueOf(cal.get(Calendar.YEAR));
				list.add(new AutoCompleteElement(year, year));
				cal.add(Calendar.YEAR, -1);
			}
			return list;
		}finally{
			logger.info("OUT < getAgnos");
		}
	}
	
	protected Session getCurrentSession() {
		return sessionFactory.getCurrentSession();
	}
}
